﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
   public class Query
    {
        /// <summary>
        /// 获取系统配置信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetConfigInfo()
        {
            string sql = string.Format("select * from {0}", "tb_config");
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, CommandType.Text);
        }
        /// <summary>
        /// 内容检索，获取指定表的指定ID的内容
        /// </summary>
        public static DataTable QueryOne(string table, int ID)
        {
            string sql = string.Format("select * from {0} where ID=@ID", table);
            SqlParameter[] parameters = {
                                            new SqlParameter("ID", SqlDbType.Int)
                                        };
            parameters[0].Value = ID;
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, parameters, CommandType.Text);
        }
        /// <summary>
        /// 内容检索，模型中指定栏目ID的列表
        /// </summary>
        public static DataTable QuerySomeByCid(string table, int Cid,string sortField,string sortValue)
        {
            
            string sql = string.Format("select * from {0} where ColumnID={1} order by {2} {3}",table,Cid,sortField,sortValue);     
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql,CommandType.Text);
        }
        /// <summary>
        /// 内容检索，模型中指定栏目ID的一定数目的列表
        /// </summary>
        public static DataTable QueryAnyByCid(string count ,string table, int Cid, string sortField, string sortValue)
        {

            string sql = string.Format("select {0} from {1} where ColumnID={2} order by {3} {4}",count, table, Cid, sortField, sortValue);
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, CommandType.Text);
        }

        /// <summary>
        /// 内容检索，模型中指定栏目ID的和条件的列表
        /// </summary>
        public static DataTable QueryIfByCid(string count, string table, int Cid, string ifWord, string sortField, string sortValue)
        {

            string sql = string.Format("select {0} from {1} where ColumnID={2} and {3} order by {4} {5}", count, table, Cid, ifWord, sortField, sortValue);
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, CommandType.Text);
        }
        /// <summary>
        /// 内容检索，按照一定的顺序
        /// </summary>
        public static DataTable QueryOrder(string table,string Sort,string way)
        {
            string sql = string.Format("select * from {0}  order by {1} {2}", table, Sort, way);       
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql,CommandType.Text);
        }
        public static DataTable QueryALL(string table)
        {
            string sql = string.Format("select * from " + table);
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, CommandType.Text);
        }
        /// <summary>
        /// 获取指定栏目ID的所有子栏目
        /// </summary>
        public static DataTable QueryColumnALL(int ParentID)
        {
            string sql = string.Format("select * from tb_column where ParentID=@ParentID order by OrderID asc");
            SqlParameter[] parameters = {
                                            new SqlParameter("ParentID", SqlDbType.Int)
                                        };
            parameters[0].Value = ParentID;
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, parameters, CommandType.Text);
        }
       /// <summary>
       /// 登录验证用户信息
       /// </summary>
       /// <param name="Name"></param>
       /// <param name="Password"></param>
       /// <returns></returns>
        public static DataTable QueryOneUser(string Name, string Password)
        {
            string sql = string.Format("select * from tb_user where Name=@Name and Password=@Password and UserEnabled=1");
            SqlParameter[] parameters = {
                                            new SqlParameter("Name", SqlDbType.NVarChar, 100),
                                            new SqlParameter("Password", SqlDbType.NVarChar, 100)
                                        };
            parameters[0].Value = Name;
            parameters[1].Value = Password;
            Utility.SQLHelper db = new Utility.SQLHelper();
            return db.ExecuteQuery(sql, parameters, CommandType.Text);
        }
    }
}
